【レポート】Amazon Redshift と Redshift Spectrumによるデータウェアハウスのベストプラクティス #reinvent #ABD304

原題

ABD304-Best Practices for Data Warehousing with Amazon Redshift & Redshift Spectrum

概要

Most companies are over-run with data, yet they lack critical insights to make timely and accurate business decisions. They are missing the opportunity to combine large amounts of new, unstructured big data that resides outside their data warehouse with trusted, structured data inside their data warehouse. In this session, we take an in-depth look at how modern data warehousing blends and analyzes all your data, inside and outside your data warehouse without moving the data, to give you deeper insights to run your business. We will cover best practices on how to design optimal schemas, load data efficiently, and optimize your queries to deliver high throughput and performance.

ほとんどの企業はデータを使いすぎていますが、タイムリーで正確なビジネス上の意思決定を行うための重要な洞察が欠けています。彼らは、データウェアハウス外の大量の新しい非構造化大容量データを、データウェアハウス内の信頼できる構造化データと結合する機会を逃しています。このセッションでは、最新のデータウェアハウジングがデータを移動することなく、データウェアハウスの内部と外部のすべてのデータをブレンドして分析し、ビジネスを実行するためのより深い洞察を提供する方法を詳しく見ていきます。最適なスキーマを設計し、データを効率的にロードし、クエリを最適化して高いスループットとパフォーマンスを実現する方法に関するベストプラクティスについて説明します。

登壇

Tony Gibbs - Senior Data Warehousing Solutions Architect, Amazon Web Services

セッションレポート

Amazon Redshift ベストプラクティス オーバービュー

• 歴史と開発
• コンセプトとテーブル設計
• データストレージ、データロード（Ingestion）、ETL
• ノードタイプとクラスタのサイジング
• 追加情報

歴史と開発

Redshiftは、PostgreSQLをベースにデータベースエンジンを新規に開発しました。ストレージエンジンはカラムナストレージエンジンであり、MPP（Massively Parallel Processing）という機構を用いたシェアード・ナッシングアーキテクチャです。シェアード・ナッシングによって、128台まで線形スケールを実現しています。その後、分析用の関数、暗号化、IAM対応、バックアップ等様々な機能を追加して現在に至っています。 ２週間毎にパッチや機能アップデートが自動提供され、指定した時間帯の30分間に更新を適用します。

コンセプトとテーブル設計

Amazon Redshift のアーキテクチャは以下のとおりです。RedshiftはODBC/JDBCを提供しており、PostgreSQLのODBC/JDBCはサポートしていませんが、PostgreSQLのPythonドライバ（libpq + psycopg2）は正しく接続できます。利用者はLeader Node に接続してテーブルを作成します。Leader Nodeはクエリの解析や調整をしてpg_catalogに保存します。この例では3つのCompute Nodeを持ち、DMLはこのノードが実行します。Compute Node全てが並列に単一のクエリを実行します。データのロードやバックアップするとき、Compute NodeはS3と直接データをやり取りできます。紫色のノードはSpectrum layerで、S3上のデータを直接スキャンするクエリユニットです。Spectrum layerはプロビジョニング不要です。

データストレージ

カラムナ(Columnar)

集計や分析に必要なカラムのみをスキャンすることでIOを削減します。

圧縮（Compression）

データをカラム毎にカラムに適した圧縮タイプで圧縮することでIOを効果的に削減します。COPYコマンドはデフォルトで自動的にカラムデータの圧縮と統計情報を取得します。データを格納した後からでもANALYZE COMPRESSIONという診断コマンドで最適な圧縮タイプを見つけることができます。このコマンドのヘルパースクリプト（Amazon Redshift Column Encoding Utility）も提供されていますのでご利用できます。

筆者補足：Amazon Redshift Column Encoding Utilityは、すでにロードされたデータを使用して、作成済みのスキーマに最適な列エンコーディングを適用できます。実行すると、スキーマまたは個々のテーブル全体が分析されます。ANALYZE COMPRESSION コマンドを使用して、表内の列のいずれかが更新する必要があるかどうかを判断し、必要であれば、最適な変換先のスクリプトが生成されます。

ゾーンマップ（Zone Maps）

ブロックごとのキーの最大値・最小値のメタ情報をメモリ上に持つことで、不要なブロックを読み飛ばし、IOを削減します。

データのソート（Data Sorting）

クエリが早く実行させるのに効果的な単調増加のゾーンマップによりIOを削減します。ソート済みキーは範囲スキャンを利用して必要なデータのみを効果的に抜き出すことができます。ソートキーの選定は、クエリパターン、ビジネス要件、データの特徴に応じて選択してください。ソートキーは、カーディナリティが低いカラムではブロック読み出しが広範囲に渡る可能性があるので、カーディナリティが高いカラムを（第一）ソートキーに指定してください。以下の例では、クエリ条件のキーをソートすることで、Sorted by date（右側）は少ない読み出しで済むように改善されています。

筆者補足：第一ソートキーはパフォーマンスの観点から非圧縮にすることを推奨します。

スライス（Slices）

スライスは仮想的なコンピュートノードのようになります。スライスはコンピュートノードの種類ごとに2、16、32スライスの何れかに分割されます。スライス毎にデータは分割され、並列に処理されます。レコードはスライスに分散され、スライスはパーティション内のデータのみを処理します。

データの分散（Data Distribution）

EVEN

データはラウンドロビンでRedshiftクラスタを構成するコンピュートノードに均等に配置されます。以下のスライドのようにラウンドロビンで均等にレコードが配置されます。

KEY(DISTKEY)

データは分散キーが同じもので分散して配置されます。うまく機能すると、高速化が期待できますが、その反面、コンピュートノードでのデータの偏りが発生する可能性があります。Example#1では、分散キーにカーディナリティが低いカラムを指定したため、データに偏りが生じています。

Example#2では、分散キーにカーディナリティが高いカラムを指定したため、データに偏りが改善しています。

KEY(DISTKEY)は、テーブルの結合、集計、INSERT INTO ... SELECTのユースケースにおいてパフォーマンスの改善が期待できます。

ALL

すべてのノードにデータをコピーします。小さいサイズのテーブル(3M行未満)などはALLを利用してデータを配置しておきます。大きいテーブルに対しては、EVENやKEYを適用します。ALLはテーブルの結合においてパフォーマンスの改善が期待できます。

テーブル設計のまとめ

• ディメンションテーブルからファクトテーブルにフィルタリングされた列をマテリアライズする
• 計算（集計）した値をマテリアライズする
• 一時的なカラムの分散キーを避ける
• 文字列型や数値型において、不必要に大きな型にしない
• カラムはANALYZE COMMPLATIONなどを利用して、最適に圧縮する
• フィルタ条件に用いられるプライマリキーをソートキーに追加する

データストレージ、データロード（Ingestion）、ETL

ディスク

• Redshiftはローカルのアタッチド・ストレージデバイスを利用する。コンピュートノードは２〜３倍のストレージキャパシティを持っている
• ディスクは２つのパーテションに分割され、ローカルデータとミラーデータによってアクセスされる
• パーティションはRAWデバイスで、ローカルストレージデバイスは本質的に一時的なもの、シングルノードでは1つのノードで複数のディスク障害に耐性がある

冗長化（Redundancy）

• クラスタでデータ冗長化が保証される場合、グローバルコミットは全てのパーマネントテーブルが他のノードのブロックに書き込まれるのが保証される
• S3に非同期バックアップ（一貫性のあるスナップショット）、バックアップサイクルは5GB以上の変更もしくは８時間毎、ユーザーが任意に取得もできる
• テンポラリテーブルは、リモートパーティションで冗長化されず、書き込みパフォーマンスが２倍、フルコミットやバックアップ対象外
• テーブルごとにバックアップの対象から除外することができる（DDLにNO BACKUPオプション）

トランザクション（Transactions）

• RedshiftはACIDなフルトランザクションで、分離レベルはserializable、２フェーズコミット
• クラスタのコミットの統計情報が取得できる（commit_stats.sql）
• コミットのオーバーヘッドは負担が大きいので、明示的なトランザクション生成によってコミットを制限することを考慮する

COPY文（Data ingestion: Copy Statement）

• データを入れるスループットは、スライスのクエリのプロセッサが１つのファイルをロードするまでの時間：
  • ストリーミング圧縮
  • パース
  • 分散
  • 書き込み
• 入力ファイルはスライス数に分割した方が良い。
• スライス数が16の場合、ファイルを16に分割するとデータをロードするパフォーマンスが最大化する
• COPYはノード数を負増やすと線形スケールする
• 分割したファイルはgzip圧縮したサイズが1MB〜1GBである必要がある
• その他、ファイルのデリミタやよく使うCOPY文のオプションも紹介されています。

Redshift Spectrum（Data ingestion: Redshift Spectrum）

• S3の外部テーブルに対して、INSERT INTO SELECT 使う
  • ロードする追加ファイルフォーマット：PARQUET、ORC、Grok
  • 入力データの集計
  • and/or行カラムのサブセットを選択する
  • SQLで入力カラムデータを操作する
• ベストプラクティス
  • ELTよりもクラスタリソースの照会やレポーティングの保存
  • フィルタや集計する入力データはCOPYよりもパフォーマンスが改善する
• 設計の注意点
  • S3に対する読み込みの繰り返しはトランザクショナルではない
  • 読み込んだデータに対して、$5/TB

データ読み込みに関する設計上の注意点（Design Considerations: Data ingestion）

• 大きな書き込みのための設計
  • バッチ処理システム、巨大なデータの集計処理のための最適化する
  • 1MBサイズちょっとのイミュータブルブロックは、フラグメントしないように書き込み時にブロックをクローンすることを意味する
  • 小さな書き込み（~1-10 rows）は大きな書き込み（~100K rows）とコストが変わらない
• UPDATE と DELETE
  • イミュタブルブロックはUPDATEとDELETEによる論理削除だけを意味する
  • テーブルから不要な行を削除するため、VACUUMやDEEP COPYをしなければならない

重複排除/UPSERT（Data ingestion: Deduplication/UPSERT）

Redshift内のテーブルに、S3の外部テーブルの内容をマージしたい場合、

事前に突き合わせをして、Redshift内のレコードを削除した後、外部テーブルのデータをディープコピーする

ベストプラクティス: ELT

• ワークフローやステートメントは、明示的なトランザクションで囲む
• DELETEの代わりにDROP TABLE もしくは TRUNCATEの利用を考慮する
• ステージングテーブル
  • 一時テーブルもしくは"BACKUP NO"指定をしたパーマネントテーブル
  • ステージングテーブルと本番テーブル間でINSERT INTO SELECT構文をスピードアップするなら同じ分散キーを指定する
  • 自動列圧縮オプション（デフォルト）を無効（"COMPUPDATE OFF"）に設定する
  • 本番テーブルはCOPYの圧縮を設定する、もしくはANALYZE COMPRESSION statementを使う
  • CREATE TABLE LIKE もしくは DDLから列圧縮を書き込む
  • 膨大な行（1億レコード以上）をCOPYするためには、INSERT INTO SELECT の代わりにALTER TABLE APPENDを使うことを考慮する

VACUUM と ANALYZE

• VACUUMはテーブル全体にソートをかけて、そして、削除マーカーが付いている行を削除します。
  • テーブルがソートされている場合、挿入オペレーションは新しいデータをローカルでソートして、未ソートリージョンに書き込む
• ANALYZEは最適なクエリプランのためのテーブルの統計情報を集める

• ベストプラクティス：

  • VACUUMは必要なときのみ実行する
  • 一般に夜間や週末など閑散期に実行する
  • 大規模もしくは広範囲のテーブルに対してディープコピーを検討する
  • ANALYZEはフィルタ条件に使うカラムを挿入した後に走らせなければならない
  • クラスタの全てのテーブルに対してVACUUMとANALYZEを実行するユーティリティ
    • AnalyzeVacuumUtility

Terminology and Concept

ノードタイプは、ディスク重視型のDSとコンピュート重視型のDCの２種類あります。

ノードタイプとクラスタのサイジング

ベストプラクティス： クラスタのリサイジング

• 本番環境ではデータをミラーリングするため、少なくとも２台のコンピュートノードで構成する
  • リーダーノードは追加費用は発生しない
• RedshiftはEC2Classic環境よりもVPC環境のほうが明らかに早い
• メンテナンスには、最低20%の空き領域もしくは最大テーブルの約３倍の容量が必要
• テーブルを再作成する、再作成するためのスクラッチ領域
• VACUUMで再ソートするテーブルのためのフリースペースが必要
• 中間クエリ結果のための、一時テーブル領域を使う

利用できる Redshift Spectrum ノードの最大数は、Redshiftクラスタの中のスライス数です。もし、DC1インスタンスからDC2インスタンスにアップグレードすると、 - DC1と同じ費用で明らかに早くなる - リザーブドインスタンス（RI）は移行しても自動適用されない

参考文献

AWS Labs on GitHub - Amanzon Redshift

• amazon-redshift-utils
• amazon-redshift-monitoring
• amazon-redshift-udfs

AWS Bigdata Blog - Amanzon Redshift

• Amazon Redshift Engineering’s Advanced Table Design Playbook: Preamble, Prerequisites, and Prioritization
• Amazon Redshiftのパフォーマンスチューニングテクニック Top 10
• Amazon Redshift Spectrum 10 のベストプラクティス

感想

毎年恒例、非常に評判の高いRedshiftのセッションの2017年版です。今回は内容がリバイズしただけでなく、Redshift Spectrumにも触れられています。参考文献は日本語のリンクを記載にしました。Amazon Redshiftのパフォーマンスチューニングテクニック Top 10とAmazon Redshift Spectrum 10 のベストプラクティスはとても良い内容なので合わせて読んで下さい。